package com.spark.sql

import org.apache.spark.SparkConf
import org.apache.spark.sql._

/**
  *
  * @author Pop
  * @date 2022/7/20 22:48
  */
object Spark07_SparkSql_Test {
  def main(args: Array[String]): Unit = {
    System.setProperty("HADOOP_USER_NAME", "root")
    // 创建SparkSql的运行环境
    val sparkSql: SparkConf = new SparkConf().setMaster("local[*]").setAppName("sparkSql")
    val session: SparkSession = SparkSession.builder()
      .enableHiveSupport()//启动hive支持，很重要
      .config(sparkSql).getOrCreate()
    // 先创建一个数据库
    session.sql(
      """
        |use popbase
        |""".stripMargin)
   // 准备数据 建表语句
    session.sql(
      """
        |CREATE TABLE `user_visit_action`(
        | `date` string,
        | `user_id` bigint,
        | `session_id` string,
        | `page_id` bigint,
        | `action_time` string,
        | `search_keyword` string,
        | `click_category_id` bigint,
        | `click_product_id` bigint,
        | `order_category_ids` string,
        | `order_product_ids` string,
        | `pay_category_ids` string,
        | `pay_product_ids` string,
        | `city_id` bigint)
        | row format delimited fields terminated by '\t'
        |""".stripMargin
    )
    // 插入数据
    session.sql(
      """
        |load data local inpath 'datas/user_visit_action.txt' into table
        |popbase.user_visit_action
        |""".stripMargin
    )

    session.sql(
      """
        |CREATE TABLE `product_info`(
        | `product_id` bigint,
        | `product_name` string,
        | `extend_info` string)
        |row format delimited fields terminated by '\t'
        |""".stripMargin
    )

    session.sql(
      """
        |    load data local inpath 'datas/product_info.txt' into table popbase.product_info
        |""".stripMargin
    )

    session.sql(
      """
        | CREATE TABLE `city_info`(
        | `city_id` bigint,
        | `city_name` string,
        | `area` string)
        |row format delimited fields terminated by '\t'
        |""".stripMargin
    )

    session.sql(
      """
        |load data local inpath 'datas/city_info.txt' into table popbase.city_info
        |""".stripMargin)


    // 简单的查询一下
    session.sql("select * from city_info").show()

    session.close()
  }

}
